package vn.aptech.leaveManagementSystem.business.model;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import vn.aptech.leaveManagementSystem.business.dto.Application;

public class Applications extends vn.aptech.leaveManagementSystem.dao.DBManager{
	
	public int addApplication(Application app) throws SQLException
	{
		getConn();
		String sql="{Call sp_InsertApplication(?,?,?,?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, app.getEmpID());
		cs.setDate(2, app.getStartDate());
		cs.setDate(3, app.getEndDate());
		cs.setString(4, app.getReason());
		cs.setString(5,app.getContact());
		cs.setInt(6, app.getStatus());
		return cs.executeUpdate();
	}
	
	public int updateApplicationStatus(int idApp, int newStatus) throws SQLException
	{
		getConn();
		String sql="{Call sp_UpdateApplicationStatus(?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idApp);
		cs.setInt(2, newStatus);
		return cs.executeUpdate();	
	}
	
	public ResultSet getApplication(int idApp) throws SQLException
	//[Application].*,
	//FirstName,LastName,Gender,Birthday,JoinDate,
	//IDDepartment,DepartmentName,IDPosition,PositionName
	{
		getConn();
		String sql="{Call sp_getApplication(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idApp);
		return cs.executeQuery();
	}
	
	public ResultSet getLastedApplication(int idEmp) throws SQLException
	//[Application].*,
	//FirstName,LastName,Gender,Birthday,JoinDate,
	//IDDepartment,DepartmentName,IDPosition,PositionName
	{
		getConn();
		String sql="{Call sp_getLastedApplication(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		return cs.executeQuery();
	}
	
	public ResultSet getApplicationsNotApproveForDepartmentManager(int idDep) throws SQLException
	//[Application].*,
	//FirstName,LastName,Gender,Birthday,JoinDate,
	//IDDepartment,DepartmentName,IDPosition,PositionName
	{
		getConn();
		String sql="{Call sp_getApplicationsNotApproveForDepartmentManager(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idDep);
		return cs.executeQuery();
	}
	
	public ResultSet getApplicationsNotApproveForDirector() throws SQLException
	//[Application].*,
	//FirstName,LastName,Gender,Birthday,JoinDate,
	//IDDepartment,DepartmentName,IDPosition,PositionName
	{
		getConn();
		String sql="{Call sp_getApplicationsNotApproveForDirector}";
		CallableStatement cs=conn.prepareCall(sql);
		return cs.executeQuery();
	}
	
	
	public ResultSet getLeaveHistory(int idEmp) throws SQLException
	{
		getConn();
		String sql="{Call sp_getLeaveHistory(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		return cs.executeQuery();	
	}
	
}
